﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using bdnt_pets_system_Entity;
using System.Data;

namespace bdnt_pets_system_Bo.DataManager
{
    /// <summary>
    /// 车辆管理
    /// </summary>
    public class VehicleManager
    {
        //AccessHelperSQL accessHelper = AccessHelperSQL.GetInstance();
        AccessHelper accessHelper = AccessHelper.GetInstance();
        List<Tbl_Vehicle> ConvertTableToObjs(DataTable dt)
        {
            List<Tbl_Vehicle> returnlist = new List<Tbl_Vehicle>();
            foreach (DataRow dr in dt.Rows)
            {
                returnlist.Add(new Tbl_Vehicle
                {
                    Id = Convert.ToInt32(dr["Id"]),
                    VINNo = dr["VINNo"] == DBNull.Value ? "" : dr["VINNo"].ToString(),
                    ProducitonNo = dr["ProducitonNo"] == DBNull.Value ? "" : dr["ProducitonNo"].ToString(),
                    VColor = dr["VColor"] == DBNull.Value ? "" : dr["VColor"].ToString(),
                    VPhase = dr["VPhase"] == DBNull.Value ? "" : dr["VPhase"].ToString(),
                    VType = dr["VType"] == DBNull.Value ? "" : dr["VType"].ToString(),
                    Remark = dr["Remark"] == DBNull.Value ? "" : dr["Remark"].ToString(),
                    VConfiguration = dr["VConfiguration"] == DBNull.Value ? "" : dr["VConfiguration"].ToString(),
                    en_VConfigurationName = dr["en_VConfigurationName"] == DBNull.Value ? "" : dr["en_VConfigurationName"].ToString(),
                    VConfigurationName = dr["VConfigurationName"] == DBNull.Value ? "" : dr["VConfigurationName"].ToString(),
                    InteriorColor = dr["InteriorColor"] == DBNull.Value ? "" : dr["InteriorColor"].ToString()
                });
            }
            return returnlist;
        }

        public List<Tbl_Vehicle> GetObjListByCondition(Tbl_Vehicle obj, int pageSize, int currentPage)
        {
            StringBuilder sql = new StringBuilder(@"select row_number() over(order by Id desc) recordId,r.*
                ,(select DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) VConfigurationName 
                ,(select en_DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) en_VConfigurationName from Tbl_Vehicle r where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.VINNo))
                    sql = sql.AppendFormat(@" and VINNo like '%{0}%'", obj.VINNo);
                if (!string.IsNullOrEmpty(obj.ProducitonNo))
                    sql = sql.AppendFormat(@" and ProducitonNo like '%{0}%'", obj.ProducitonNo);
                if (!string.IsNullOrEmpty(obj.VColor))
                    sql = sql.AppendFormat(@" and VColor like '%{0}%'", obj.VColor);
                if (!string.IsNullOrEmpty(obj.VPhase))
                    sql = sql.AppendFormat(@" and VPhase like '%{0}%'", obj.VPhase);
                if (!string.IsNullOrEmpty(obj.VType))
                    sql = sql.AppendFormat(@" and VType like '%{0}%'", obj.VType);
            }
            int startNum = pageSize * (currentPage > 0 ? currentPage - 1 : 0);
            int endNum = pageSize * currentPage;
            sql.Insert(0, "select * from (");
            sql.AppendFormat(@") re where re.recordId>{0} and re.recordId<={1}", startNum, endNum);
            var dt = accessHelper.ExcuteDataTable(sql.ToString());
            return ConvertTableToObjs(dt);
        }

        public int GetObjListByConditionCount(Tbl_Vehicle obj)
        {
            StringBuilder sql = new StringBuilder("select count(0) allCount from Tbl_Vehicle where 1=1");
            if (obj != null)
            {
                if (!string.IsNullOrEmpty(obj.VINNo))
                    sql = sql.AppendFormat(@" and VINNo like '%{0}%'", obj.VINNo);
                if (!string.IsNullOrEmpty(obj.ProducitonNo))
                    sql = sql.AppendFormat(@" and ProducitonNo like '%{0}%'", obj.ProducitonNo);
                if (!string.IsNullOrEmpty(obj.VColor))
                    sql = sql.AppendFormat(@" and VColor like '%{0}%'", obj.VColor);
                if (!string.IsNullOrEmpty(obj.VPhase))
                    sql = sql.AppendFormat(@" and VPhase like '%{0}%'", obj.VPhase);
                if (!string.IsNullOrEmpty(obj.VType))
                    sql = sql.AppendFormat(@" and VType like '%{0}%'", obj.VType);
            }
            var dt = accessHelper.ExecuteScalar(sql.ToString());
            if (dt != null)
                return Int32.Parse(dt.ToString());
            return 0;
        }

        public bool SaveObj(Tbl_Vehicle cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
                sql = string.Format(@"INSERT INTO Tbl_Vehicle
           (VINNo,ProducitonNo,VColor,VPhase,VType,Remark,VConfiguration,InteriorColor)
            VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", cuObj.VINNo, cuObj.ProducitonNo, cuObj.VColor, cuObj.VPhase,
            cuObj.VType, cuObj.Remark, cuObj.VConfiguration, cuObj.InteriorColor);

            else sql = string.Format(@"UPDATE Tbl_Vehicle
               SET VINNo ='{0}'
                  ,ProducitonNo ='{1}'
                 ,VColor = '{2}'
                 ,VPhase = '{3}' 
                ,VType = '{4}' 
                ,Remark = '{5}'   
                ,VConfiguration = '{6}'
             ,InteriorColor = '{7}'
             WHERE Id={8}", cuObj.VINNo, cuObj.ProducitonNo, cuObj.VColor, cuObj.VPhase,
                          cuObj.VType, cuObj.Remark, cuObj.VConfiguration, cuObj.InteriorColor, cuObj.Id);
            return accessHelper.ExcultData(sql);
        }

        /// <summary>
        /// -1已经存在车辆
        /// </summary>
        /// <param name="cuObj"></param>
        /// <returns></returns>
        public Int32 SaveVObj(Tbl_Vehicle cuObj)
        {
            string sql = "";
            if (cuObj.Id == 0)
            {
                sql = string.Format(@"select Id from Tbl_Vehicle r 
                where (r.vinno='{0}' or r.producitonNo='{1}') ", cuObj.VINNo, cuObj.ProducitonNo);
                var dt = accessHelper.ExcuteDataTable(sql);
                if (dt != null && dt.Rows.Count > 0)
                {
                    return -1;
                }
                sql = string.Format(@"INSERT INTO Tbl_Vehicle
           (VINNo,ProducitonNo,VColor,VPhase,VType,Remark,VConfiguration,InteriorColor)
            VALUES('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}')", cuObj.VINNo, cuObj.ProducitonNo, cuObj.VColor, cuObj.VPhase,
           cuObj.VType, cuObj.Remark, cuObj.VConfiguration, cuObj.InteriorColor);

            }
            else sql = string.Format(@"UPDATE Tbl_Vehicle
               SET VINNo ='{0}'
                  ,ProducitonNo ='{1}'
                 ,VColor = '{2}'
                 ,VPhase = '{3}' 
                ,VType = '{4}' 
                ,Remark = '{5}'   
                ,VConfiguration = '{6}'
             ,InteriorColor = '{7}'
             WHERE Id={8}", cuObj.VINNo, cuObj.ProducitonNo, cuObj.VColor, cuObj.VPhase,
                          cuObj.VType, cuObj.Remark, cuObj.VConfiguration, cuObj.InteriorColor, cuObj.Id);
            return accessHelper.ExcultData(sql) ? 1 : 0;
        }

        public Tbl_Vehicle GetObjById(int objId)
        {
            Tbl_Vehicle cur = new Tbl_Vehicle();
            var sql = string.Format(@"select r.*,
(select DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) VConfigurationName 
                ,(select en_DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) en_VConfigurationName from Tbl_Vehicle r where Id={0}", objId);
            var dt = accessHelper.ExcuteDataTable(sql);
            return ConvertTableToObjs(accessHelper.ExcuteDataTable(sql.ToString())).FirstOrDefault();
        }

        public Tbl_Vehicle GetNextVehicleByProNo(string productionNo)
        {
            Tbl_Vehicle cur = new Tbl_Vehicle();
            var sql = string.Format(@"select r.*,(select DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) VConfigurationName 
                ,(select en_DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) en_VConfigurationName from Tbl_Vehicle r   
            where producitonNo=(select Min(producitonNo) cuProdNo 
            from Tbl_Vehicle where producitonNo>'{0}')", productionNo);
            return ConvertTableToObjs(accessHelper.ExcuteDataTable(sql.ToString())).FirstOrDefault();
        }

        public Tbl_Vehicle GetVehicleByProNoOrVinNo(string productionNo, string vinNo)
        {            
            StringBuilder sql = new StringBuilder(@"select r.*,(select DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) VConfigurationName 
            ,(select en_DictName from tbl_dictInfo where pid=28 and dictValue=VConfiguration and rownum=1) en_VConfigurationName from Tbl_Vehicle r  
             where rownum=1 ");
            if (!string.IsNullOrEmpty(productionNo))
            {
                sql.AppendFormat(@" and ProducitonNo = '{0}'", productionNo);
            }

            if (!string.IsNullOrEmpty(vinNo))
            {
                sql.AppendFormat(@" and VINNo = '{0}'", vinNo);
            }
            return ConvertTableToObjs(accessHelper.ExcuteDataTable(sql.ToString())).FirstOrDefault();
        }


        public bool DeleteCuObj(Int32 objId)
        {
            string sql = string.Format(@"delete from Tbl_Vehicle where Id={0}", objId);
            return accessHelper.ExcultData(sql);
        }

    }
}
